use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Roles')
  drop table Roles
go

create table Roles
(
  Id int identity(1,1) not null,
  RoleName nvarchar(100) not null,
  Positions_Edit bit not null,
  Positions_ViewId int not null,
  Territories_Edit bit not null, 
  Territories_ViewId int not null, 
  CommodityGroups_Edit bit not null,
  CommodityGroups_ViewId int not null,
  Users_Edit bit not null,
  Users_ViewId int not null,
  Employers_Edit bit not null,
  Employers_ViewId int not null,
  Vacancies_Edit bit not null,
  Vacancies_ViewId int not null,
  Candidates_Edit bit not null,
  Candidates_ViewId int not null
)  
go

alter table Roles add constraint PK_Roles_ID primary key(Id)
go

---

alter table Roles add constraint FK_Roles_ViewVariants_Positions 
      foreign key (Positions_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Roles_Positions_Edit
      default (0) for Positions_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Territories 
      foreign key (Territories_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Roles_Territories_Edit
      default (0) for Territories_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_CommodityGroups 
      foreign key (CommodityGroups_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Roles_CommodityGroups_Edit
      default (0) for CommodityGroups_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Users 
      foreign key (Users_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Roles_Users_Edit
      default (0) for Users_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Employers 
      foreign key (Employers_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Employers_Users_Edit
      default (0) for Employers_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Vacancies 
      foreign key (Vacancies_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Vacancies_Edit
      default (0) for Vacancies_Edit
go

----

alter table Roles add constraint FK_Roles_ViewVariants_Candidates 
      foreign key (Candidates_ViewId) references ViewVariants(Id) 
go

alter table Roles with check add constraint DF_Candidates_Edit
      default (0) for Candidates_Edit
go